package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DBConnection {

	// static private String strDriver = "com.mysql.jdbc.Driver";
	// static private String strUrl = "jdbc:mysql://localhost:3306/lxf";
	// static private String strUser = "root";
	// static private String strPwd = "123456";

	static private String resourceName = "jdbc/hzlibrary";
	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;

	/*
	 * static { try { Class.forName(strDriver); } catch (ClassNotFoundException
	 * ex) { System.out.println("Error load" + strDriver); } }
	 */

	public DBConnection() {
	}

	public ResultSet query(String sql) {
		try {
			rs = getStatement().executeQuery(sql);
		} catch (SQLException ex) {
			System.err.println("query error:" + ex.getMessage());
		}
		return rs;
	}

	public ResultSet query(String sql, ArrayList paramList) {
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(sql);
			for (int i = 0; i < paramList.size(); i++) {
				ps.setObject(i + 1, paramList.get(i));
			}
			rs = ps.executeQuery();
		} catch (SQLException ex) {
			System.err.println("query error:" + ex.getMessage());
		}
		return rs;
	}

	public boolean queryBatch(ArrayList sqls) {
		boolean bRet = false;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			for (int i = 0; i < sqls.size(); i++) {
				String sql = (String) sqls.get(i);
				stmt.addBatch(sql);
			}
			stmt.executeBatch();
			conn.commit();
			bRet = true;
		} catch (Exception ex) {
			try {
				conn.rollback();
			} catch (Exception ex2) {
				System.err.println("rollback failed:" + ex2.getMessage());
			}
			ex.printStackTrace();
		} finally {
		}
		return bRet;
	}

	public int Update(String sql) {
		int resultNum = 0;
		try {
			resultNum = getStatement().executeUpdate(sql);
		} catch (SQLException ex) {
			System.err.println("update error:" + sql + ex.getMessage());
		} finally {
		}
		return resultNum;
	}

	public int Update(String sql, ArrayList paramList) {
		int rn = 0;
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(sql);
			for (int i = 0; i < paramList.size(); i++) {

				ps.setObject(i + 1, paramList.get(i));
			}
			rn = ps.executeUpdate();
		} catch (SQLException ex) {
			System.err.println("update error:" + sql + ex.getMessage());
		} finally {
		}
		return rn;
	}

	public int UpdateReturnId(String sql, ArrayList paramList) {
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(sql,
					Statement.RETURN_GENERATED_KEYS);
			for (int i = 0; i < paramList.size(); i++) {
				ps.setObject(i + 1, paramList.get(i));
			}
			ps.executeUpdate();
		} catch (SQLException ex) {
			System.err.println("UpdateReturnId error:" + sql + ex.getMessage());
		} finally {
		}
		int i = 0;
		try {
			ResultSet rs = ps.getGeneratedKeys();
			rs.next();
			i = rs.getInt(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	public void commit() {
		try {
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}

	public void close() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (Exception ex) {
			System.err.println("close error:" + ex.getMessage());
		}
	}

	private Connection getConnection() {
		try {
			if (conn == null || conn.isClosed()) {
				// conn = DriverManager.getConnection(strUrl, strUser, strPwd);
				Context initContext = new InitialContext();
				Context envContext = (Context) initContext
						.lookup("java:/comp/env");
				DataSource ds = (DataSource) envContext.lookup(resourceName);
				conn = ds.getConnection();
				conn.setAutoCommit(false);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
		return conn;
	}

	private Statement getStatement() {
		try {
			if (stmt == null)
				stmt = getConnection().createStatement();
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
		return stmt;
	}

	/**
	 * resultSetToList
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> resultSetToList(ResultSet rs)
			throws SQLException {
		if (rs == null)
			return null;
		ResultSetMetaData md = rs.getMetaData();
		int columnCount = md.getColumnCount();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Map<String, Object> rowData = null;
		while (rs.next()) {
			rowData = new HashMap<String, Object>(columnCount);
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(rowData);
		}
		return list;
	}

}
